package com.chidopi.market.bookstore.dao.impl;

import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.Arrays;
import java.util.List;

import org.slf4j.Logger;
import org.slf4j.LoggerFactory;
import org.springframework.jdbc.core.RowMapper;
import org.springframework.jdbc.core.simple.ParameterizedRowMapper;

import com.chidopi.cdf.dao.AbstractBaseDaoImpl;
import com.chidopi.market.bookstore.domain.Book2City;
import com.chidopi.market.bookstore.domain.BookCities;
import com.chidopi.market.bookstore.domain.City2Category;
import com.chidopi.market.bookstore.domain.ContextProvider;


public class BookCitiesDaoImpl extends AbstractBaseDaoImpl<BookCities>  {
	
	private static Logger logger = LoggerFactory.getLogger(BookCitiesDaoImpl.class);

	@Override
	public int insert(BookCities t) {
		String sql = "insert into bookcities (CITYNAME, CITYPREFIX, CP_ID, ENABLE_FLAG , IMAGE_MODE, BIZMODE, CREATE_DATE, CREATE_BY) "
				+ "values (:cityname, :cityprefix , :cp.id, :enable_flag, :image_mode, :bizmode, NOW(), :create_by)";
		int ret = super.insert(sql, t);		
		if (ret > 0) {
			t.setId(ret);
			updateDetails(t,false);
		}
		return ret;
	}

	private void updateDetails(BookCities t, boolean isUpdate) {
		
		if(isUpdate){
			// delete old city2category, book2city
			String sql ="delete from city2category where CITY_ID = ? ";
			super.simpleJdbcTemplate.update(sql, t.getId());
			
			sql = "delete from book2city where CITY_ID = ?" ;
			super.simpleJdbcTemplate.update(sql, t.getId());
		}
		
		List<City2Category> categories = t.getCategories();
		if(null != categories){
			String sql = "insert into city2category (CITY_ID, CATEGORY_ID, ENABLE_FLAG, PARENT_ID, LEVEL)" +
					     " values (?, ?, ?, ?, ?)";
			for(City2Category cc : categories){
				super.simpleJdbcTemplate.update(sql, t.getId(), cc.getCategory_id(), cc.getEnable_flag(), cc.getParent_id(), cc.getLevel());
			}
		}
		
		List<Book2City> books = t.getBooks();
		if(null != books){
			String sql = "insert into book2city (BOOK_ID, CITY_ID, ENABLE_FLAG, SALE_PRICE, SUBSCRIBE_QUOTA) " +
					     " values (?, ?, ?, ?, ?)";
			for(Book2City bc : books){
				super.simpleJdbcTemplate.update(sql, bc.getBook_id(), t.getId(), bc.getEnable_flag(), bc.getSale_price(), bc.getSubscribe_quota());
			}
		}
		
	}

	@Override
	public int update(BookCities t) {
		String sql = "update bookcities set CITYNAME = :cityname, CITYPREFIX = :cityprefix, CP_ID = :cp.id, ENABLE_FLAG = :enable_flag, IMAGE_MODE = :image_mode, BIZMODE = :bizmode, update_by = :update_by where id= :id";
		int ret =  super.execSQL(sql, t);
		if (ret > 0) {			
			updateDetails(t,true);
		}
		return ret;
	}

	@Override
	public int delete(BookCities t) {
		String sql = "delete from bookcities where id = :id";	
		
		int ret = super.execSQL(sql, t);
		
		if (ret > 0) {
			sql ="delete from city2category where CITY_ID = ? ";
			super.simpleJdbcTemplate.update(sql, t.getId());
			
			sql = "delete from book2city where CITY_ID = ?" ;
			super.simpleJdbcTemplate.update(sql, t.getId());
		}
		return ret;
	}

	@Override
	public List<BookCities> find(String sqlWhere, Object... args) {
		if (sqlWhere == null || sqlWhere.trim().equals("")) {
			String sql = "select a.*, b.CPNAME from bookcities a left join contextprovider b on a.CP_ID= b.ID order by a.id ";
			return super.simpleJdbcTemplate.query(sql, this.getRowMapper());
		} else {
			logger.debug("sqlWhere:[" + sqlWhere + "]/params:" + Arrays.asList(args).toString());
			String sql = "select a.*, b.CPNAME from bookcities a left join contextprovider b on a.CP_ID= b.ID where " + sqlWhere + " order by a.id";
			logger.debug("sql: " + sql);
			List<BookCities> list = super.simpleJdbcTemplate.query(sql, this.getRowMapper(), args);
			if (list == null) {
				logger.debug("query null!!!");
			} else {
				for (BookCities city : list) {
					logger.debug("id=" + city.getId() + "/cityName=" + city.getCityname());
				}
			}
			return list;
		}
	}

	@Override
	public BookCities findById(Object id) {
		String sql = "a.id=?";
		List<BookCities> list = this.find(sql, id);
		if (0 == list.size()) return null;
		return list.get(0);
	}

	@Override
	protected String getTable() {
		return "bookcities";
	}

	@Override
	protected String getSequenceName() {
		return null;
	}

	@Override
	protected RowMapper<BookCities> getRowMapper() {
		return new ParameterizedRowMapper<BookCities>(){
			@Override
			public BookCities mapRow(ResultSet rs, int rowNum)	throws SQLException {
				BookCities vo = new BookCities();
				vo.setId(rs.getInt("ID"));
				vo.setCityname(rs.getString("CITYNAME"));
				vo.setCityprefix(rs.getString("CITYPREFIX"));
				vo.setEnable_flag(rs.getString("ENABLE_FLAG"));
				vo.setImage_mode(rs.getString("IMAGE_MODE"));
				vo.setBizmode(rs.getString("BIZMODE"));
				
				ContextProvider cp = new ContextProvider();
				cp.setId(rs.getInt("CP_ID"));
				cp.setCpName(rs.getString("CPNAME"));
				vo.setCp(cp);
				return vo;
			}
		};	
    }

}
